package cn.com.codes.common.service.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;

import cn.com.codes.common.service.DatabaseUpgradeService;
import cn.com.codes.framework.app.services.BaseServiceImpl;
import cn.com.codes.framework.jdbc.JdbcTemplateWrapper;
import cn.com.codes.framework.security.SysLog;
import cn.com.codes.object.IterationList;
import cn.com.codes.object.OperaLog;
import cn.com.codes.object.OtherMission;
import cn.com.codes.object.SingleTestTask;
import cn.com.codes.object.TypeDefine;
import cn.com.codes.object.User;

public class DatabaseUpgradeServiceImpl extends BaseServiceImpl implements DatabaseUpgradeService {

	private static Logger loger = Logger.getLogger(DatabaseUpgradeServiceImpl.class);
	
	static List<String >  sqlList = new ArrayList<String >();
	static List<String >  cleanDemoSqlList = new ArrayList<String >();
	static {
		sqlList.add("ALTER TABLE T_USER MODIFY COLUMN `MY_HOME` VARCHAR(300)");
		sqlList.add("INSERT INTO `t_function` VALUES ('91', '环境', '-1', '2', '0', null, '91', '/env/envAction!envList.action', '2019-06-03 12:15:34', '2019-06-03 11:16:18', '0', '/env/envAction!envList.action')");
		//sqlList.add("INSERT INTO `t_role_function_real` VALUES ('402880ea25b62b6a0125b66873290012', '91', '2019-06-03 11:16:18', '2019-06-03 11:16:18');");
		
		//----above exe in before v3.3.0 
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`, `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) VALUES ('234', 'BUG来源分布统计', '143', '2', '0', '', '234', '/singleTestTask/singleTestTaskAction!swTestTask4Report.action?dto.repTemplet=bugSourceDistbuStat_750_540_750_540', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '1', 'bugSourceDistbuStat')");
		//sqlList.add("INSERT INTO `t_role_function_real` (`ROLEID`, `FUNCTIONID`, `INSERTDATE`, `UPDATEDATE`) VALUES ('402880ea25b62b6a0125b66873290012', '234', '2019-07-01 14:23:32', '2019-07-01 14:23:32')");
		//------about exe in before v3.3.1 
		sqlList.add("delete  from t_typedefine where INDENTIFIER=2 and status_flg !=1");
		sqlList.add("delete  from t_typedefine where INDENTIFIER=1 and status_flg !=1");
		sqlList.add("insert into t_role_function_real(`FUNCTIONID`, `ROLEID`) SELECT t.`FUNCTIONID` ,'402880ea25b62b6a0125b66873290012' FROM t_function t where FUNCTIONID not in (\r\n" + 
				"SELECT `FUNCTIONID`  FROM t_role_function_real where roleid='402880ea25b62b6a0125b66873290012')\r\n" + 
				"");
		//------above exe in before v3.3.5
		
		
		sqlList.add("ALTER TABLE T_OUTLINEINFO MODIFY COLUMN `MODULENAME` VARCHAR(150)");
		
		//------above exe in before v3.3.6
		
		
		sqlList.add("update  t_function set seq = 90   where FUNCTIONID = 11");
		sqlList.add("update  t_testcasepackage  set   expected_start_time=str_to_date('2019-08-18', '%Y-%m-%d %H')   where  id='402881cb68eaeb470168eb0d5dcd0015'");
		sqlList.add("delete  from t_typedefine where INDENTIFIER = 6 and enumid in(134,47,32,33,34,53,73,77,78,79,80,85,86,87,89,90,91,92,95,96,98,102,118,119,120,121,122,123,76,83,88,101)");
		
		sqlList.add("CREATE OR REPLACE VIEW `v_expcaseview` AS " + 
				"select `t`.`TESTCASEID` AS `testcaseid`,`o2`.`MODULENAME` AS `superMelName`,`o`.`MODULENAME` AS `modulename`," + 
				"`t`.`TESTCASEDES` AS `testcasedes`,`t`.`OPERDATA_RICHTEXT` AS `testcaseoperdata`,`t`.`EXPRESULT` AS `EXPRESULT`," + 
				"`t`.`STATUS` AS `status`,`u`.`NAME` AS `author`,`u2`.`NAME` AS `exeName`,`tp`.`ENUMNAME` AS `typeNmae`," + 
				"`tpri`.`ENUMNAME` AS `priName`,`t`.`UPDDATE` AS `UPDDATE`, o2.MODULELEVEL as super_level\r\n" + 
				"from ((((((`t_testcasebaseinfo` `t` join `t_outlineinfo` `o` on((`o`.`MODULEID` = `t`.`MODULEID`))) " + 
				"join `t_outlineinfo` `o2` on((`o2`.`MODULEID` = `o`.`SUPERMODULEID`))) join " + 
				"`t_user` `u` on((`t`.`CREATERID` = `u`.`ID`))) left join " + 
				"`t_user` `u2` on((`t`.`ADUIT_ID` = `u2`.`ID`))) join " + 
				"`t_typedefine` `tp` on((`tp`.`ENUMID` = `t`.`CASETYPE`))) join " + 
				"`t_typedefine` `tpri` on((`tpri`.`ENUMID` = `t`.`CASE_PRI`))) order by `t`.`UPDDATE` desc ");
		
		sqlList.add("CREATE OR REPLACE  VIEW v_expbugview AS \r\n" + 
				"select `t`.`BUGCARDID` AS `bugcardid`,`t`.`BUGDESC` AS `bugDesc`,`t`.`REPRODUCT_TXT` AS `reproduct_txt`,`t`.`CURRENT_STATE` AS `current_state`,`tplevel`.`ENUMNAME` AS `levelName`,`tptype`.`ENUMNAME` AS `typeName`,`tpocca`.`ENUMNAME` AS `occaName`,`urep`.`NAME` AS `reptName`,`t`.`BUGDISVDATE` AS `reptDate`,`vrep`.`VERSION_NUM` AS `disVer`,`ufix`.`NAME` AS `fixName`,`t`.`FIX_DATE` AS `fixDate`\r\n" + 
				",`ucls`.`NAME` AS `clsName`,`t`.`CURRENT_HANDL_DATE` AS `current_handl_date`,`vres`.`VERSION_NUM` AS `reslVer`,`o`.`MODULENAME` AS `modulename`,`o2`.`MODULENAME` AS `superMelName`\r\n" + 
				",`currHand`.NAME as `currName`\r\n" + 
				" from ((((((((((`t_bugbaseinfo` `t` join `t_typedefine` `tplevel` on((`tplevel`.`ENUMID` = `t`.`BUGLEVEL`))) join `t_typedefine` `tptype` on((`tptype`.`ENUMID` = `t`.`BUGTYPE`))) join `t_typedefine` `tpocca` on((`tpocca`.`ENUMID` = `t`.`BUGOCCA`)))\r\n" + 
				" join `t_user` `urep` on((`t`.`BUGDISVPERID` = `urep`.`ID`)))\r\n" + 
				" join `t_software_version` `vrep` on((`vrep`.`VERSION_ID` = `t`.`DISCOVER_VER`)))\r\n" + 
				" left join `t_user` `ufix` on((`ufix`.`ID` = `t`.`DEV_OWNER`)))\r\n" + 
				" left join `t_user` `ucls` on((`ucls`.`ID` = `t`.`BUG_NUM`)))\r\n" + 
				" left join `t_user` `currHand` on((`currHand`.`ID` = `t`.`CURRENT_HANDLER`)))\r\n" + 
				" left join `t_software_version` `vres` on((`vres`.`VERSION_ID` = `t`.`FIX_VERSION`)))\r\n" + 
				" join `t_outlineinfo` `o` on((`o`.`MODULEID` = `t`.`MODULEID`)))\r\n" + 
				" join `t_outlineinfo` `o2` on((`o2`.`MODULEID` = `o`.`SUPERMODULEID`)) order by `t`.`CURRENT_HANDL_DATE` desc;\r\n" + 
				" ");
		//----above exe in 4.1.0
		
		sqlList.add("INSERT INTO `t_function` VALUES ('92', '维护', '91', '2', '1', '', '91', '/env/envAction!envList.action', '2019-06-03 12:15:34', '2019-06-03 11:16:18', '0', 'envAction!envList')");
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`, `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) VALUES ('235', '查看', '234', '3', '1', '', '235', 'analysisAction!bugSourceDistbuStat', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '1', 'analysisAction!bugSourceDistbuStat')");
		sqlList.add("insert into t_role_function_real(`FUNCTIONID`, `ROLEID`) SELECT t.`FUNCTIONID` ,'402880ea25b62b6a0125b66873290012' FROM t_function t where FUNCTIONID not in (\r\n" + 
				"SELECT `FUNCTIONID`  FROM t_role_function_real where roleid='402880ea25b62b6a0125b66873290012')\r\n" + 
				"");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!getWriteCaseDayTrend'  ,PAGE='0' where t.FUNCTIONID='146' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!reptFixCloseDayTrend' ,PAGE='0'  where t.FUNCTIONID='148' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!devDayFixTrend'  ,PAGE='0' where t.FUNCTIONID='160' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!testerDayCommitTrend'  ,PAGE='0' where t.FUNCTIONID='162' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!commitExistBugDayStat'  ,PAGE='0' where t.FUNCTIONID='164' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!commitExistBugBuildStat'  ,PAGE='0' where t.FUNCTIONID='166' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!testerExeCaseDayTrend'  ,PAGE='0' where t.FUNCTIONID='168' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!bugFixPersonStat'  ,PAGE='0' where t.FUNCTIONID='180' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!testerBugStat'  ,PAGE='0' where t.FUNCTIONID='182' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!bugExistDayStat'  ,PAGE='0' where t.FUNCTIONID='184' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!bugExistWeekStat'  ,PAGE='0' where t.FUNCTIONID='186' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!bugExistDay4NoFixStat'  ,PAGE='0' where t.FUNCTIONID='188' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!bugExistWeek4NoFixStat'  ,PAGE='0' where t.FUNCTIONID='200' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!bugTypeStat'  ,PAGE='0' where t.FUNCTIONID='202' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!bugGradeStat'  ,PAGE='0' where t.FUNCTIONID='204' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!bugImpPhaseStat'  ,PAGE='0' where t.FUNCTIONID='206' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!bugBequeathStat'  ,PAGE='0' where t.FUNCTIONID='208' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!testerBugQuality'  ,PAGE='0' where t.FUNCTIONID='220' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!bugDensityStat'  ,PAGE='0' where t.FUNCTIONID='222' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!bugModuleDistbuStat'  ,PAGE='0' where t.FUNCTIONID='224' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!bugStatusDistbuStat'  ,PAGE='0' where t.FUNCTIONID='226' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!bugSummary'  ,PAGE='0' where t.FUNCTIONID='228' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!chargeOwner'  ,PAGE='0' where t.FUNCTIONID='230' ");
		sqlList.add("update t_function t set t.SECURITY_URL= 'analysisAction!importCase'  ,PAGE='0' where t.FUNCTIONID='232' ");
		sqlList.add("update t_function t set PAGE='0' where t.FUNCTIONID='235' ");
		//----above exe in 4.1.1
		sqlList.add("CREATE OR REPLACE VIEW `v_expcaseview` AS " + 
				"select `t`.`TESTCASEID` AS `test_case_id`,`o2`.`MODULENAME` AS `super_mname`,`o`.`MODULENAME` AS `module_name`, " + 
				"`t`.`TESTCASEDES` AS `test_case_des`,`t`.`OPERDATA_RICHTEXT` AS `test_case_oper_data`,`t`.`EXPRESULT` AS `exp_result`," + 
				"`t`.`STATUS` AS `status`,`u`.`NAME` AS `author`,`u2`.`NAME` AS `exe_name`,`tp`.`ENUMNAME` AS `type_name`," + 
				"`tpri`.`ENUMNAME` AS `pri_name`,`t`.`UPDDATE` AS `UPDDATE`, o2.MODULELEVEL as super_module_level  " + 
				" from ((((((`t_testcasebaseinfo` `t` join `t_outlineinfo` `o` on((`o`.`MODULEID` = `t`.`MODULEID`))) " + 
				" join `t_outlineinfo` `o2` on((`o2`.`MODULEID` = `o`.`SUPERMODULEID`))) join \r\n" + 
				" `t_user` `u` on((`t`.`CREATERID` = `u`.`ID`))) left join " + 
				" `t_user` `u2` on((`t`.`ADUIT_ID` = `u2`.`ID`))) join " + 
				" `t_typedefine` `tp` on((`tp`.`ENUMID` = `t`.`CASETYPE`))) join " + 
				" `t_typedefine` `tpri` on((`tpri`.`ENUMID` = `t`.`CASE_PRI`))) order by `t`.`UPDDATE` desc ");
		
		sqlList.add("alter table t_testcasebaseinfo modify column TESTCASEOPERDATA text ");
		sqlList.add("alter table t_testcasebaseinfo modify column OPERDATA_RICHTEXT text ");
		//----above exe in 4.2.1
		sqlList.add("insert INTO t_case_lib_category (category_id,category_name,category_level,isleaf_node,super_id,category_state,company_id,category_num,creat_date,upd_date)VALUES(100,'根目录',1,1,0,0,'1289630248328','0','2020-01-06 10:34:39','2020-01-06 10:34:39')");
		
		sqlList.add("alter table t_case_Lib_test_case_info modify column oper_data text ");
		sqlList.add("alter table t_case_Lib_test_case_info modify column exp_result text ");
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
				                             + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
				                             + "VALUES ('236', '用例库', '-1', '1', '0', '', '236', "
				                             + "'url', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'url')");
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('237', '目录维护', '236', '2', '0', '', '237', "
                + "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', '')");
		
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('238', '目录增加', '237', '3', '1', '', '238', "
                + "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'testCaseLibAction!addNodes')");
		
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('239', '目录修改', '237', '3', '1', '', '239', "
                + "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'testCaseLibAction!updateNode')");
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('240', '目录删除', '237', '3', '1', '', '240', "
                + "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'testCaseLibAction!deleteNode')");
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('241', '目录拖拽移动', '237', '3', '1', '', '241', "
                + "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'testCaseLibAction!move')");
		
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('242', '目录同级上移', '237', '3', '1', '', '242', "
                + "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'testCaseLibAction!itemMoveUp')");
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('243', '目录同级下移', '237', '3', '1', '', '243', "
                + "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'testCaseLibAction!itemMoveDown')");	
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('244', '用例维护', '236', '2', '0', '', '244', "
                + "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', '')");
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('245', '增加用例', '244', '3', '1', '', '245', "
                + "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'testCaseLibAction!saveCase')");
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('246', '修改用例', '244', '3', '1', '', '246', "
                + "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'testCaseLibAction!updateCase')");
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('247', '删除用例', '244', '3', '1', '', '247', "
                + "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'testCaseLibAction!deleteCase')");
		
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('248', '导入用例', '244', '3', '1', '', '248', "
                + "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'caseImpAction!caseLibImpCase')");
		
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('249', '导出用例', '244', '3', '1', '', '249', "
                + "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'caseImpExpAction!caseLibExpCase')");
		sqlList.add("insert into t_role_function_real(`FUNCTIONID`, `ROLEID`) SELECT t.`FUNCTIONID` ,'402880ea25b62b6a0125b66873290012' FROM t_function t where FUNCTIONID not in (\r\n" + 
				"SELECT `FUNCTIONID`  FROM t_role_function_real where roleid='402880ea25b62b6a0125b66873290012')\r\n" + 
				"");
		//----above exe in  4.3.0

		//----above exe in  4.4.0
		sqlList.add("CREATE OR REPLACE VIEW v_all_buginfo AS " + 
				"SELECT " + 
				"	b2.*, sv1.SEQ AS disSeq, " + 
				"	ifnull(sv2.SEQ, 99999) AS fixSeq, " + 
				"	sv2.VERSION_ID AS fixVerId, " + 
				"	sfInfo.VERSION_ID AS exitVerId " + 
				" FROM " + 
				"	t_bugbaseinfo b2 " + 
				" JOIN t_software_version sv1 ON sv1.VERSION_ID = b2.DISCOVER_VER " + 
				" LEFT JOIN t_software_version sv2 ON b2.fix_version = sv2.VERSION_ID " + 
				" AND b2.TASK_ID = sv2.TASKID " + 
				" JOIN t_software_version sfInfo ON sfInfo.TASKID = b2.task_id " + 
				" AND sfInfo.SEQ <=" + 
				" IF (" + 
				"	ifnull(sv2.SEQ, 99999) < sv1.SEQ," + 
				"	sv1.SEQ," + 
				"	ifnull(sv2.SEQ, 99999)" + 
				")");
		
		sqlList.add("UPDATE t_function  F SET F.FUNCTIONNAME='开发人员bug处理简报' WHERE F.FUNCTIONNAME='责任人分析'");
		sqlList.add("UPDATE t_function  F SET F.FUNCTIONNAME='开发人员bug引入原因分析' WHERE F.FUNCTIONNAME='责任人引入原因分析'");
		sqlList.add("UPDATE t_function  F SET F.FUNCTIONNAME='测试人员简报' WHERE F.FUNCTIONNAME='简要统计'");
		//Bug重开明细
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('170', '开发人员重开Bug明细', '143', '2', '0', '', '170', "
                + "'/singleTestTask/singleTestTaskAction!swTestTask4Report.action?dto.repTemplet=bugReOpenStat_750_540', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '1', 'analysisAction!getbugReOpenStat')");
		sqlList.add("update t_function t set t.FUNCTIONNAME= '开发人员重开Bug明细'  where t.FUNCTIONNAME='Bug重开明细' ");
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
		+ " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
		+ "VALUES ('171', '查看', '170', '3', '1', '', '171', "
		+ "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'analysisAction!getbugReOpenStat')");
		
		//费解Bug明细
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
                + " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
                + "VALUES ('172', '测试人员费解Bug明细', '143', '2', '0', '', '172', "
                + "'/singleTestTask/singleTestTaskAction!swTestTask4Report.action?dto.repTemplet=obscureBugStat_750_540', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '1', 'analysisAction!getObscureBugStat')");
		
		sqlList.add("INSERT INTO `t_function` (`FUNCTIONID`, `FUNCTIONNAME`, `PARENTID`, `LEVELNUM`, `ISLEAF`, `METHODS`, `SEQ`,"
		+ " `URL`, `INSERTDATE`, `UPDATEDATE`, `PAGE`, `SECURITY_URL`) "
		+ "VALUES ('173', '查看', '172', '3', '1', '', '173', "
		+ "'', '2019-07-01 15:14:57', '2019-07-01 15:14:57', '0', 'analysisAction!getObscureBugStat')");
		
		sqlList.add("insert into t_role_function_real(`FUNCTIONID`, `ROLEID`) SELECT t.`FUNCTIONID` ,'402880ea25b62b6a0125b66873290012' FROM t_function t where FUNCTIONID not in (\r\n" + 
				"SELECT `FUNCTIONID`  FROM t_role_function_real where roleid='402880ea25b62b6a0125b66873290012')\r\n" + 
				"");

		sqlList.add("alter table t_exe_history modify column testresult int ");
		
		sqlList.add("update t_exe_history set  opera_type=1 where opera_type is null ");
		sqlList.add("update  t_exe_history set logic_delete =0 where logic_delete is null  ");
		
		sqlList.add("update  T_USER set is_ldap ='0' where is_ldap is null  ");
		
		sqlList.add("alter table  T_USER DROP INDEX T_USER_UQ ");
		
		sqlList.add("alter table  T_USER ADD UNIQUE T_USER_UQ   (`LOGINNAME`, `DELFLAG`, `is_ldap`)  USING BTREE ");
		
		////////----above exe in  4.5.0  system_or_ldap_flg 
		
	}
	
	static {
		cleanDemoSqlList.add("delete from t_iteration_list where  task_id='402881cb68eaeb470168eaf675660001'");
		cleanDemoSqlList.add("delete from t_other_mission  where (project_id='402881cb68eaeb470168eaf675660001' or charge_person_id='1289630248984') and create_time <='2019-07-22'");
		cleanDemoSqlList.add("delete from  t_testcase_casepkg  where packageid in (select id from t_testcasepackage  where taskid='402881cb68eaeb470168eaf675660001') ");
		cleanDemoSqlList.add("delete from t_user_testcasepkg  where packageid in (select id from t_testcasepackage  where taskid='402881cb68eaeb470168eaf675660001') ");
		cleanDemoSqlList.add("delete from t_testcasepackage  where taskid='402881cb68eaeb470168eaf675660001'");
		cleanDemoSqlList.add("delete from  t_testcasepkg_operation_history where  task_id='402881cb68eaeb470168eaf675660001'");
		cleanDemoSqlList.add("delete from    t_case_bug_rela  where BUGCARDID in (select BUGCARDID from t_bugbaseinfo where task_id='402881cb68eaeb470168eaf675660001')");
		cleanDemoSqlList.add("delete from    t_case_bug_rela  where TESTCASEID in (select TESTCASEID from  t_testcasebaseinfo where taskid='402881cb68eaeb470168eaf675660001')");
		cleanDemoSqlList.add("delete from  t_bugbaseinfo  where  task_id='402881cb68eaeb470168eaf675660001'");
		cleanDemoSqlList.add("delete from   t_testresult where taskid='402881cb68eaeb470168eaf675660001'");
		cleanDemoSqlList.add("delete from   t_testcasebaseinfo where taskid='402881cb68eaeb470168eaf675660001'");
		cleanDemoSqlList.add("delete from     t_outlineinfo  where taskid='402881cb68eaeb470168eaf675660001'");
		cleanDemoSqlList.add("delete from     t_outlineinfo  where taskid='2c93e0816c2f6e28016c2f6ede020001'");
		cleanDemoSqlList.add("delete from  t_task_useactor where taskid in ('2c93e0816c2f6e28016c2f6ede020001','402881cb68eaeb470168eaf675660001')");
		cleanDemoSqlList.add("delete from  t_single_test_task where taskid in ('2c93e0816c2f6e28016c2f6ede020001','402881cb68eaeb470168eaf675660001')");
		//cleanDemoSqlList.add("update T_USER set MY_HOME =null  where MY_HOME like  '%402881cb68eaeb470168eaf675660001%'");
		cleanDemoSqlList.add("update T_USER set MY_HOME =null ");
		cleanDemoSqlList.add("delete  from t_typedefine where INDENTIFIER = 6 and enumid in(134,47,32,33,34,53,73,77,78,79,80,85,86,87,89,90,91,92,95,96,98,102,118,119,120,121,122,123,76,83,88,101)");
		
		
		cleanDemoSqlList.add("delete from t_broadmsg_recip_rela ");
		cleanDemoSqlList.add("delete from t_broadcast_msg ");
		cleanDemoSqlList.add("delete from t_user_role_real  where  userid='2c9679416a9a53ae016a9a5d666e0002'");
		cleanDemoSqlList.add("delete from t_user  where  id='2c9679416a9a53ae016a9a5d666e0002'");
		cleanDemoSqlList.add("delete from t_user_role_real  where  userid='402881cb68e0aa530168e0e295f3000a'");
		cleanDemoSqlList.add("delete from t_user  where  id='402881cb68e0aa530168e0e295f3000a'");
		cleanDemoSqlList.add("delete from t_user_role_real  where  userid='402881cb68e0aa530168e0e2df13000b'");
		cleanDemoSqlList.add("delete from t_user  where  id='402881cb68e0aa530168e0e2df13000b'");
		cleanDemoSqlList.add("delete from t_user_role_real  where  userid='402881cb68e0aa530168e0e337ff000c'");
		cleanDemoSqlList.add("delete from t_user  where  id='402881cb68e0aa530168e0e337ff000c'");
		cleanDemoSqlList.add("delete from t_user_role_real  where  userid='402881cb68e0aa530168e0e4145f000d'");
		cleanDemoSqlList.add("delete from t_user  where  id='402881cb68e0aa530168e0e4145f000d'");
		cleanDemoSqlList.add("delete from t_user_role_real  where  userid='402881cb68e4d42c0168e4f624540001'");
		cleanDemoSqlList.add("delete from t_user  where  id='402881cb68e4d42c0168e4f624540001'");
		cleanDemoSqlList.add("delete from t_user_role_real  where  userid='402881cb68e4d42c0168e509daa60002'");  
		cleanDemoSqlList.add("delete from t_user  where  id='402881cb68e4d42c0168e509daa60002'");
	}
	public int deleteDemoData() {
		
		JdbcTemplateWrapper jdbcTemplateWrapper = super.getJdbcTemplateWrapper();
		for(String sql : cleanDemoSqlList) {
			jdbcTemplateWrapper.getJdbcTemplate().update(sql);
		}
		SysLog log = new OperaLog();
		log.setLogType(0);
		log.setOperDesc("HaveCleanDemoData");
		log.setOperSummary("HaveCleanDemoData");
		log.setOperDate(new Date());
		super.add(log);
		super.getHibernateGenericController().evict(User.class);
		super.getHibernateGenericController().evict(SingleTestTask.class);
		super.getHibernateGenericController().evict(OtherMission.class);
		super.getHibernateGenericController().evict(IterationList.class);
		super.getHibernateGenericController().evict(TypeDefine.class);

		return 1;
	}
	
	public void cleabDemoDataCache() {
		

	}
	
	@Override
	public int exeUpgrade() {
		
		
		int exeFlg = 0;
		
		return exeFlg ;
	}

	public int  exeSql() {
		
		
		
		return 0;
	}
	
	//和 exeSql 一样，只是不打日志，用于启动时自动执行 
	public int exeSqlNoPringLog  (String sql ) {
		try {
			JdbcTemplateWrapper jdbcTemplateWrapper = super.getJdbcTemplateWrapper();
			
			jdbcTemplateWrapper.getJdbcTemplate().getDataSource().getConnection().createStatement().execute(sql);
		} catch (DataAccessException e) {
			//e.printStackTrace();
			return 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}
		return 1;
	}
	public int  exeSql(String sql ) {
		try {
			JdbcTemplateWrapper jdbcTemplateWrapper = super.getJdbcTemplateWrapper();
			//jdbcTemplateWrapper.getJdbcTemplate().update(sql);
			jdbcTemplateWrapper.getJdbcTemplate().execute(sql);
		} catch (DataAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}
		return 1;
	}


	public static List<String> getSqlList() {
		return sqlList;
	}

	public static void setSqlList(List<String> sqlList) {
		DatabaseUpgradeServiceImpl.sqlList = sqlList;
	}
	public static List<String> getCleanDemoSqlList() {
		return cleanDemoSqlList;
	}
}
